import pandas as pd
import os
import tkinter as tk
from datetime import datetime
from tkinter import ttk
from tkinter import messagebox

def get_excel_files(path, path_return=False):
    '''
    获取指定目录下的所有Excel文件路径
    并将其出库或入库数量汇总到一个字典中
    '''
    excel_files = []

    for root, dirs, files in os.walk(path):
        for file in files:
            if file.endswith(('.xlsx', '.xls')):
                file_path = os.path.join(root, file)
                excel_files.append(file_path)
    dictionary = {}

    for file_path in excel_files:
        df = pd.read_excel(file_path).values.tolist()
        for i in df:
            if i[1] in dictionary:
                dictionary[i[1]] += float(i[2])
            else:
                dictionary[i[1]] = float(i[2])
    if path_return:
        return excel_files
    return dictionary

def get_year_out_date():
    '''
    获取本年出库物品及其对应出库日期
    '''
    out_path = get_excel_files('./出库', True)
    datenow = datetime.now().strftime("%Y")
    out_date_list = []
    for i in out_path:
        df = pd.read_excel(i).values.tolist()
        out_date = i.replace(f"./出库\\{datenow}", "").replace("\\", "").replace(".xlsx", "")
        out_date = out_date[:2] + '月' +out_date[2:4] + '日'
        for j in df:
            if out_date_list == []:
                out_date_list.append([j[1], out_date])
                continue
            for k in out_date_list:
                if k[0] == j[1]:
                    if k[1] != out_date:
                        k[1] += ', ' + out_date
                elif not any(j[1] in sublist for sublist in out_date_list):
                    out_date_list.append([j[1], out_date])
    return out_date_list

def day_summary():
    '''
    当按钮被点击时
    生成日汇总文件
    '''
    file_list = [["物品", "剩余数量", "总入库数量", "总出库数量", "本日入库数量", "本日出库数量"]]

    dictionary_in = get_excel_files('./入库')
    dictionary_out = get_excel_files('./出库')

    for i, j in dictionary_in.items():
        if i in dictionary_out.keys():
            file_list.append([i, j-dictionary_out[i], j, dictionary_out[i]])
        else:
            file_list.append([i, j, j, 0])

    datenow = datetime.now().strftime("%Y/%m/%d.xlsx")
    if os.path.exists(f"./入库/{datenow}"):
        month_in = pd.read_excel(f"./入库/{datenow}").values.tolist()
        for i in month_in:
            for x in file_list[1:]:
                if x[0] == i[1]:
                    x.append(i[2])
    else:
        for x in file_list[1:]:
            x.append(0)

    if os.path.exists(f"./出库/{datenow}"):
        month_out = pd.read_excel(f"./出库/{datenow}").values.tolist()
        for i in month_out:
            for x in file_list[1:]:
                if x[0] == i[1]:
                    x.append(i[2])
    else:
        for x in file_list[1:]:
            x.append(0)

    for x in file_list[1:]:
        if len(x) != len(file_list[0]):
            for i in range(len(file_list[0])-len(x)):
                x.append(0)

    date = datetime.now().strftime("%Y-%m-%d")
    try:
        df = pd.DataFrame(file_list[1:], columns=file_list[0])
        df.to_excel(f"./汇总/日汇总/{date}_日汇总.xlsx", index=False)
        messagebox.showinfo("提示", f"日汇总生成完成, 请查看\"./汇总/日汇总/{date}_日汇总.xlsx\"文件")
    except:
        messagebox.showerror("错误", "日汇总生成失败!")

def month_summary():
    '''
    当按钮被点击时
    生成月汇总文件
    '''
    file_list = [["物品", "剩余数量", "总入库数量", "总出库数量", "本月入库数量", "本月出库数量"]]

    dictionary_in = get_excel_files('./入库')
    dictionary_out = get_excel_files('./出库')

    for i, j in dictionary_in.items():
        if i in dictionary_out.keys():
            file_list.append([i, j-dictionary_out[i], j, dictionary_out[i]])
        else:
            file_list.append([i, j, j, 0])

    datenow = datetime.now().strftime("%Y/%m")
    if os.path.exists(f"./入库/{datenow}"):
        month_in = get_excel_files(f"./入库/{datenow}")
        for i, j in month_in.items():
            for x in file_list[1:]:
                if x[0] == i:
                    x.append(j)
    else:
        for x in file_list[1:]:
            x.append(0)

    if os.path.exists(f"./出库/{datenow}"):
        month_out = get_excel_files(f"./出库/{datenow}")
        for i, j in month_out.items():
            for x in file_list[1:]:
                if x[0] == i:
                    x.append(j)
    else:
        for x in file_list[1:]:
            x.append(0)

    for x in file_list[1:]:
        if len(x) != len(file_list[0]):
            for i in range(len(file_list[0])-len(x)):
                x.append(0)

    date = datetime.now().strftime("%Y-%m")
    try:
        df = pd.DataFrame(file_list[1:], columns=file_list[0])
        df.to_excel(f"./汇总/月汇总/{date}_月汇总.xlsx", index=False)
        messagebox.showinfo("提示", f"月汇总生成完成, 请查看\"./汇总/月汇总/{date}_月汇总.xlsx\"文件")
    except:
        messagebox.showerror("错误", "月汇总生成失败")

def year_summary():
    '''
    当按钮被点击时
    生成年汇总文件
    '''
    file_list = [["物品", "剩余数量", "总入库数量", "总出库数量", "本年入库数量", "本年出库数量", "出库时间"]]
    dictionary_in = get_excel_files('./入库')
    dictionary_out = get_excel_files('./出库')

    for i, j in dictionary_in.items():
        if i in dictionary_out.keys():
            file_list.append([i, j-dictionary_out[i], j, dictionary_out[i]])
        else:
            file_list.append([i, j, j, 0])

    datenow = datetime.now().strftime("%Y")
    if os.path.exists(f"./入库/{datenow}"):
        year_in = get_excel_files(f"./入库/{datenow}")
        for i, j in year_in.items():
            for x in file_list[1:]:
                if x[0] == i:
                    x.append(j)
    else:
        for x in file_list[1:]:
            x.append(0)

    if os.path.exists(f"./出库/{datenow}"):
        year_out = get_excel_files(f"./出库/{datenow}")
        for i, j in year_out.items():
            for x in file_list[1:]:
                if x[0] == i:
                    x.append(j)
    else:
        for x in file_list[1:]:
            x.append(0)

    for x in file_list[1:]:
        if len(x) != 6:
            for i in range(6-len(x)):
                x.append(0)
    
    out_date_list = get_year_out_date()
    for i in file_list[1:]:
        for j in out_date_list:
            if i[0] == j[0]:
                i.append(j[1])
    
    for i in  file_list[1:]:
        if len(i) != 7:
            for j in range(7-len(i)):
                i.append("本年无出库记录")

    try:
        df = pd.DataFrame(file_list[1:], columns=file_list[0])
        df.to_excel(f"./汇总/年汇总/{datenow}_年汇总.xlsx", index=False)
        messagebox.showinfo("提示", f"年汇总生成完成, 请查看\"./汇总/年汇总/{datenow}_年汇总.xlsx\"文件")
    except:
        messagebox.showerror("错误", "年汇总生成失败")

def summary(summary_tab):
    period_var = tk.StringVar(value='日')
    summary_text = tk.Text(summary_tab, height=15)

    summary_frame = tk.Frame(summary_tab)
    summary_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)

    btn_frame = tk.Frame(summary_frame)
    btn_frame.pack(pady=20)

    btn_day = ttk.Button(btn_frame, text="生成日汇总",
                       command=lambda: day_summary())
    btn_day.pack(pady=5, fill=tk.X)

    btn_month = ttk.Button(btn_frame, text="生成月汇总", 
                           command=lambda: month_summary())
    btn_month.pack(pady=5, fill=tk.X)

    btn_year = ttk.Button(btn_frame, text="生成年汇总", 
                          command=lambda: year_summary())
    btn_year.pack(pady=5, fill=tk.X)

    exit_frame = tk.Frame(summary_tab)
    exit_frame.pack(side=tk.BOTTOM, fill=tk.X, padx=10, pady=10)

    btn_exit = ttk.Button(exit_frame, text="退出系统",
                        command=summary_tab.winfo_toplevel().destroy)
    btn_exit.pack(side=tk.RIGHT)